using System;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Collections.Generic;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ArrivalInspection;
using RBAC.Read.Api.Application.Dto.Quality;

namespace RBAC.Read.Api.Application.Handler.ArrivalInspection
{
    public class GetArrivalInspectionListHandler : IRequestHandler<GetArrivalInspectionListCommand, APIPaging<ArrivalInspectionListDto>>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<GetArrivalInspectionListHandler> _logger;

        public GetArrivalInspectionListHandler(IDbConnectionFactory connectionFactory, ILogger<GetArrivalInspectionListHandler> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<APIPaging<ArrivalInspectionListDto>> Handle(GetArrivalInspectionListCommand request, CancellationToken cancellationToken)
        {
            var res = new APIPaging<ArrivalInspectionListDto>
            {
                Code = APIEnums.Success,
                Msg = "",
                TotalCount = 0,
                PageDatas = new List<ArrivalInspectionListDto>()
            };

            var where = new StringBuilder(" WHERE info.IsDeleted = 0 ");
            var p = new DynamicParameters();

            if (request.Id.HasValue && request.Id.Value > 0)
            {
                where.Append(" AND info.Id = @Id ");
                p.Add("Id", request.Id.Value);
            }
            if (!string.IsNullOrWhiteSpace(request.Subject))
            {
                where.Append(" AND info.Subject LIKE @Subject ");
                p.Add("Subject", $"%{request.Subject.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.DocCode))
            {
                where.Append(" AND info.DocCode LIKE @DocCode ");
                p.Add("DocCode", $"%{request.DocCode.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.Inspector))
            {
                where.Append(" AND info.Inspector LIKE @Inspector ");
                p.Add("Inspector", $"%{request.Inspector.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.PurchaseOrderNo))
            {
                where.Append(" AND info.PurchaseOrderNo LIKE @PurchaseOrderNo ");
                p.Add("PurchaseOrderNo", $"%{request.PurchaseOrderNo.Trim()}%");
            }
            if (request.InspectionDateStart.HasValue)
            {
                where.Append(" AND info.InspectionDate >= @InspectionDateStart ");
                p.Add("InspectionDateStart", request.InspectionDateStart.Value.Date);
            }
            if (request.InspectionDateEnd.HasValue)
            {
                // Include the end day fully
                where.Append(" AND info.InspectionDate < @InspectionDateEnd ");
                p.Add("InspectionDateEnd", request.InspectionDateEnd.Value.Date.AddDays(1));
            }

            // 1) 统计总数（仅主表）
            var countSql = $"SELECT COUNT(1) FROM ArrivalInspectionInfo info {where}";
            int limit = Math.Max(1, request.PageSize);
            int offset = Math.Max(0, (request.PageIndex - 1) * request.PageSize);
            p.Add("Limit", limit);
            p.Add("Offset", offset);

            using var conn = _connectionFactory.CreateConnectionWithRetry();
            var total = await conn.ExecuteScalarAsync<int>(countSql, p);
            res.TotalCount = total;
            if (total == 0)
            {
                return res;
            }

            // 2) 分页获取当前页的主表Id列表
            var idSql = $@"SELECT info.Id
FROM ArrivalInspectionInfo info
{where}
ORDER BY info.CreateTime DESC, info.Id DESC
LIMIT @Limit OFFSET @Offset";
            var ids = (await conn.QueryAsync<long>(idSql, p)).ToList();
            if (ids.Count == 0)
            {
                return res;
            }

            // 3) 获取主表记录
            var headerSql = @"SELECT
    info.Id,
    info.DocCode,
    info.Subject,
    info.InspectionDate,
    info.Inspector,
    info.Department,
    info.PurchaseOrderNo,
    info.SupplierName,
    info.BizStatus,
    info.DocStatus,
    info.CreateUser,
    info.CreateTime,
    -- Extended fields to match ArrivalInspectionListDto
    info.SupplierId,
    info.PurchaseDate,
    info.PurchaseContact,
    info.ContactPhone,
    info.Buyer,
    info.PurchaseDept,
    info.ProjectName
FROM ArrivalInspectionInfo info
WHERE info.Id IN @Ids";
            var headers = (await conn.QueryAsync<ArrivalInspectionListDto>(headerSql, new { Ids = ids })).ToList();

            // 4) 批量获取明细与附件
            var itemsSql = @"SELECT Id, InspectionId, PurchaseOrderNo, MaterialCode, MaterialName, SpecModel, Unit, Brand,
       PurchaseQty, ReceivedQty, InspectionType, InspectQty, QualifiedQty, UnqualifiedQty, InspectResult, UnqualifiedReason, Remark
FROM ArrivalInspectionItem WHERE InspectionId IN @Ids";
            var attsSql = @"SELECT Id, InspectionId, FileName, FilePath, FileSize, UploadTime, Uploader, Remark
FROM ArrivalInspectionAttachment WHERE InspectionId IN @Ids";

            var items = (await conn.QueryAsync<ArrivalInspectionItemDto>(itemsSql, new { Ids = ids })).ToList();
            var atts = (await conn.QueryAsync<ArrivalInspectionAttachmentDto>(attsSql, new { Ids = ids })).ToList();

            // 5) 组装到对应主表，并计算聚合
            var itemLookup = items.GroupBy(x => x.InspectionId).ToDictionary(g => g.Key, g => g.ToList());
            var attLookup = atts.GroupBy(x => x.InspectionId).ToDictionary(g => g.Key, g => g.ToList());

            foreach (var h in headers)
            {
                if (itemLookup.TryGetValue(h.Id, out var its))
                {
                    h.Items = its;
                    h.ItemCount = its.Count;
                    h.InspectQtySum = its.Sum(x => x.InspectQty);
                    h.QualifiedQtySum = its.Sum(x => x.QualifiedQty);
                    h.UnqualifiedQtySum = its.Sum(x => x.UnqualifiedQty);
                }
                if (attLookup.TryGetValue(h.Id, out var ats))
                {
                    h.Attachments = ats;
                    h.AttachmentCount = ats.Count;
                }
            }

            // 6) 设置结果
            res.PageDatas = headers
                .OrderByDescending(x => x.CreateTime)
                .ThenByDescending(x => x.Id)
                .ToList();
            return res;
        }
    }
}
